﻿
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.Streaming;
using NPOI.XSSF.UserModel;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading;
using System.Windows;
using System.Windows.Input;

namespace 查詢語句導出
{
    /// <summary>
    /// MainWindow.xaml 的互動邏輯
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
            this.TextBox8.IsEnabled = false;
        }
        string psaf = "";
        string psafjjkk = "";
        string path = System.AppDomain.CurrentDomain.BaseDirectory;
        List<string> arrsy = new List<string>();
        OracleConnection conn;
        private void Button_Click(object sender, RoutedEventArgs e)
        {
            if (this.button1.Content.Equals("连接"))
            {
                MessageBox.Show("请先连接数据库");
                return;
            }
            rese();
            psaf = this.textBox1.Text.ToString();
            psafjjkk = this.TextBox2.Text.ToString();
            //var act = new Action(NewMethod);
            //act.BeginInvoke(ar => act.EndInvoke(ar), null);
            Thread asd = new Thread(NewMethod);
            asd.Start();
            //  NewMethod();
        }

        private void NewMethod()
        {   
                           Int32 counts = 1048576;
                           OracleCommand cmd = conn.CreateCommand();
                           cmd.CommandText = psaf;
                           cmd.CommandType = CommandType.Text;
                           OracleDataReader reader = null;
                           try
                           {
                                this.buttonsss.Dispatcher.Invoke(new Action(() => { this.buttonsss.Content = "正在执行中。。。。"; this.buttonsss.IsEnabled = false; }));
                                reader = cmd.ExecuteReader();
                               Int32 i = 1;
                               Int32 sre = reader.FieldCount;
                               SXSSFWorkbook wkBook = new SXSSFWorkbook();
                               Int32 index = 0;
                               Int32 index1 = 0;
                               Stopwatch sw = new Stopwatch();
                               sw.Start();
                               ISheet sheet = wkBook.CreateSheet("sheet");
                               IRow rows = sheet.CreateRow(0);
                               if (arrsy.Count != 0)
                               {
                                   Debug.WriteLine("執行一下" + arrsy.Count);
                                   for (int j = 0; j < arrsy.Count; j++)
                                   {
                                       rows.CreateCell(j).SetCellValue(arrsy[j]);
                                   }
                               }

                               while (reader.Read())
                               {
                                   if (i != counts)
                                   {
                                       //在Sheet中插入创建一行
                                       IRow row = sheet.CreateRow(i);
                                       for (int isd = 0; isd < sre; isd++)
                                       {
                                           //Debug.WriteLine(" 进入for循环:" + isd);
                                           row.CreateCell(isd).SetCellValue(reader[isd].ToString()); //给单元格设置值：第一个参数(第几个单元格)；第二个参数(给当前单元格赋值)
                                       }
                                       i++;
                                       index++;
                                   }
                                   else if (i == counts)
                                   {
                                       index1++;
                                       i = 1;
                                       sheet = wkBook.CreateSheet("sheet" + index1);
                                       IRow rowsss = sheet.CreateRow(0);
                                       if (arrsy.Count != 0)
                                       {
                                           for (int j = 0; j < arrsy.Count; j++)
                                           {
                                            rowsss.CreateCell(j).SetCellValue(arrsy[j]);
                                           }
                                       }
                                   }
                               }
                               FileStream fsWrite = File.OpenWrite(path + psafjjkk + ".xlsx");  //导出时Excel的文件名
                               wkBook.Write(fsWrite);
                               fsWrite.Close(); //关闭文件流
                               wkBook.Close();  //关闭工作簿
                               fsWrite.Dispose(); //释放文件流
                               sw.Stop();
                               TimeSpan ts2 = sw.Elapsed;
                               MessageBox.Show("执行完成,文件已放到：" + path);
                this.label1.Dispatcher.Invoke(new Action(() => { this.label1.Content = "数量: " + index.ToString();  }));
                this.label2.Dispatcher.Invoke(new Action(() => { this.label2.Content = "耗时: " + Convert.ToInt32(ts2.TotalMilliseconds / 1000).ToString(); }));
                this.buttonsss.Dispatcher.Invoke(new Action(() => { this.buttonsss.Content = "执行"; this.buttonsss.IsEnabled = true; }));
                //this.label1.Content = "数量: " + index.ToString();
                //this.label2.Content = "耗时: " + Convert.ToInt32(ts2.TotalMilliseconds / 1000).ToString();
            }
                           catch (Exception esd)
                           {
                               MessageBox.Show("sql语句执行失败");
                         this.buttonsss.Dispatcher.Invoke(new Action(() => { this.buttonsss.Content = "执行"; this.buttonsss.IsEnabled = true; }));
                            return;
                           }
                           Console.Read();
                       
        }

        //開始鏈接oracle
        static OracleConnection OpenConn(string text, string text1, string text2, string text3, string text4)
        {
            OracleConnection conn = new OracleConnection();
            conn.ConnectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=" + text + ")(PORT=" + text1 + "))(CONNECT_DATA=(SERVICE_NAME=" + text2 + ")));Persist Security Info=True;User ID=" + text3 + ";Password=" + text4 + ";";
            conn.Open();
            return conn;
        }
        //關閉鏈接
        static void CloseConn(OracleConnection conn)
        {
            if (conn == null) { return; }
            try
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                conn.Dispose();
            }
        }
        private void Button_Click_1(object sender, RoutedEventArgs e)
        {
            if (this.button1.Content.ToString() == "连接")
            {
                if (this.TextBox3.Text != string.Empty && this.TextBox4.Text != string.Empty && this.TextBox2.Text != string.Empty && this.TextBox5.Text != string.Empty && this.TextBox6.Text != string.Empty && this.TextBox7.Text != string.Empty)
                {
                    try
                    {
                        conn = OpenConn(this.TextBox3.Text, this.TextBox4.Text, this.TextBox5.Text, this.TextBox6.Text, this.TextBox7.Text);
                        MessageBox.Show("链接成功");
                        this.TextBox3.IsEnabled = false;
                        this.TextBox4.IsEnabled = false;
                        this.TextBox5.IsEnabled = false;
                        this.TextBox6.IsEnabled = false;
                        this.TextBox7.IsEnabled = false;
                        this.button1.Content = "关闭";
                    }
                    catch (Exception ess)
                    {
                        MessageBox.Show(ess.Message);
                    }
                }
                else
                {
                    MessageBox.Show("数据不足，请检查");
                }
            }
            else
            {
                CloseConn(conn);
                this.TextBox3.IsEnabled = true;
                this.TextBox4.IsEnabled = true;
                this.TextBox5.IsEnabled = true;
                this.TextBox6.IsEnabled = true;
                this.TextBox7.IsEnabled = true;
                this.button1.Content = "连接";
                arrsy.Clear();
            }

        }
        //读取配置文件
        public void rese()
        {
            string str = System.Environment.CurrentDirectory;
            try
            {
                StreamReader sr = new StreamReader(str + "\\path.txt", Encoding.GetEncoding("UTF-8"));
                string content;
                while ((content = sr.ReadLine()) != null)
                {
                    arrsy.Add(content);
                }
            }
            catch (Exception ewes)
            {
                MessageBox.Show("未找到path文件;");
            }
        }


        private void TextBox_PreviewKeyDown(object sender, KeyEventArgs e)
        {
            string str = e.Key.ToString().Trim();   // 去掉字符串首尾处的空格
            char[] charBuf = str.ToCharArray();    // 将字符串转换为字符数组

            ASCIIEncoding charToASCII = new ASCIIEncoding();
            byte[] TxdBuf = new byte[charBuf.Length];    // 定义发送缓冲区；
            TxdBuf = charToASCII.GetBytes(charBuf);
            MessageBox.Show(e.Key.ToString());
            if ((e.Key >= Key.NumPad0 && e.Key <= Key.NumPad9) ||
                 (e.Key >= Key.D0 && e.Key <= Key.D9) ||
                 e.Key == Key.Back ||
                 e.Key == Key.Left || e.Key == Key.Right)
            {
                if (e.KeyboardDevice.Modifiers != ModifierKeys.None)
                {
                    e.Handled = true;
                }
            }
            else
            {
                e.Handled = true;
            }
        }

        public void limitnumber(object sender, TextCompositionEventArgs e)
        {
            Regex re = new Regex("[^0-9]+");
            e.Handled = re.IsMatch(e.Text);

            //  string str = e.Key.ToString().Trim();   // 去掉字符串首尾处的空格
            //char[] charBuf = e.Text.ToCharArray();    // 将字符串转换为字符数组

            //ASCIIEncoding charToASCII = new ASCIIEncoding();
            //byte[] TxdBuf = new byte[charBuf.Length];    // 定义发送缓冲区；
            //TxdBuf = charToASCII.GetBytes(charBuf);
            //for(int i = 0; i < TxdBuf.Length; i++)
            //{
            //    MessageBox.Show(TxdBuf[i].ToString());
            //}

        }
 
    }
    }
